"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : sqlines工具
Case Name   : sqlines工具-s参数指定mysql，select命令，查看转换情况
Create At   : 2022/01/30
@zou_jialiang0501115624
Description :
    1、sqlines安装
    2、创建测试脚本
    3、sqlines -s=mysql -t=opengauss -in={self.in_file} -out={self.out_file}
     -log={self.log_file}
    4、环境清理：卸载sqlines、删除对应文件
Expect      :
    1、sqlines安装成功
    2、脚本创建成功
    3、转换成功
       删除DISTINCTROW关键字、删除HIGH_PRIORITY、STRAIGHT_JOIN、SQL_SMALL_RESULT、
       SQL_BIG_RESULT、SQL_BUFFER_RESULT、SQL_CACHE、SQL_NO_CACHE、
       SQL_CALC_FOUND_ROWS关键字
    4、环境清理成功
History     :
    modified: 2023-04-19 by @daiguatutu 优化unzip解压命令，避免命令执行超时失败
"""

import os
import unittest

from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger


class SqlinesTest(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----- {os.path.basename(__file__)} start-----')
        self.root = Node('PrimaryRoot')
        self.in_file = f'{os.path.basename(__file__)[:-3]}.sql'
        self.out_file = f'{os.path.basename(__file__)[:-3]}_out.sql'
        self.log_file = f'{os.path.basename(__file__)[:-3]}.log'
        self.file_name = 'openGauss-tools-sqlines-master.zip'
        self.unzip_dir = self.file_name.replace('.zip', '')
        self.file_download = os.path.join(macro.FTP_PLUGINS_PATH,
                                     'sqlines', self.file_name)
        self.local_realpath = os.path.join('/root',
                                      os.path.basename(__file__)[-19:-3])
        self.local_realpath_file = os.path.join(self.local_realpath,
                                                self.file_name)

    def test_main(self):
        step_txt = '----step1:安装sqlines，expect: sqlines安装成功----'
        self.log.info(step_txt)
        self.log.info('-------------------下载软件包--------------------')
        cmd = f'rm -rf {self.local_realpath};mkdir -p {self.local_realpath};' \
            f'wget -P {self.local_realpath} {self.file_download}'
        self.log.info(cmd)
        self.root.sh(cmd)
        cmd2 = f"ls {self.local_realpath_file}|awk -F '/' '{{{{print $NF}}}}'"
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.assertEqual(self.file_name, res, '文件下载失败：' + step_txt)

        self.log.info('-------------------解压软件包--------------------')
        cmd3 = f'timeout 30s unzip -o  -d {self.local_realpath}  ' \
            f'{self.local_realpath_file}'
        self.log.info(cmd3)
        res = self.root.sh(cmd3).result()
        self.log.info(res)
        cmd4 = f"ls -ld {os.path.join(self.local_realpath,self.unzip_dir)}" \
            f"|awk -F '/' '{{{{print $NF}}}}'"
        self.log.info(cmd4)
        res = self.root.sh(cmd4).result()
        self.log.info(res)
        self.assertEqual(self.unzip_dir, res, '文件解压失败：' + step_txt)

        self.log.info('-------------------安装sqlines--------------------')
        cmd = f'cd {os.path.join(self.local_realpath,self.unzip_dir)};' \
            f'sh build.sh -i;pwd'
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertIn('Install Sqlines successfully', res,
                      'Sqlines安装失败：' + step_txt)
        tmp_path = res.strip().splitlines()[-1].strip()
        mid_path = os.path.join(tmp_path, 'bin')
        file_name = 'sqlines'
        check_file = os.path.join(mid_path, file_name)
        cmd = f"ls -l {check_file}"
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertEqual(res[3], 'x', '文件没有执行权限' + step_txt)
        self.log.info('-----------------配置sqlines环境变量-----------------')
        self.source_path = f"export PATH={mid_path}:$PATH"

        step_txt = '----step2:创建测试脚本，expect: 脚本创建成功----'
        self.log.info(step_txt)
        cmd1 = f"""cat >{self.in_file} <<EOF
        SELECT all HIGH_PRIORITY SQL_SMALL_RESULT SQL_BIG_RESULT
          SQL_BUFFER_RESULT SQL_CACHE SQL_CALC_FOUND_ROWS
         e.department_id,
         sum( salary ) 
        FROM
         t_Opengauss_Function_Sqlines_Case0067_1 e
         JOIN t_Opengauss_Function_Sqlines_Case0067_2 d 
          ON ( e.department_id = d.department_id ) 
        WHERE
         employee_id > 100 
        GROUP BY
         department_id 
        HAVING
         sum( salary )> 10000 
        ORDER BY 2 
         LIMIT 2,3;
         
        
        SELECT DISTINCT HIGH_PRIORITY SQL_SMALL_RESULT SQL_BIG_RESULT 
          SQL_BUFFER_RESULT SQL_NO_CACHE SQL_CALC_FOUND_ROWS
         e.department_id,
         sum( salary ) 
        FROM
         t_Opengauss_Function_Sqlines_Case0067_1 e
         JOIN t_Opengauss_Function_Sqlines_Case0067_2 d
          ON ( e.department_id = d.department_id ) 
        WHERE
         employee_id > 100 
        GROUP BY
         department_id 
        HAVING
         sum( salary )> 10000 
        ORDER BY 2 
         LIMIT 3 offset 2
         FOR UPDATE;
        
        
        SELECT DISTINCTROW HIGH_PRIORITY SQL_SMALL_RESULT SQL_BIG_RESULT
          SQL_BUFFER_RESULT SQL_CACHE SQL_CALC_FOUND_ROWS
         e.department_id,
         sum( salary ) 
        FROM
         t_Opengauss_Function_Sqlines_Case0067_1 e
         JOIN t_Opengauss_Function_Sqlines_Case0067_2 d 
          ON ( e.department_id = d.department_id ) 
        WHERE
         employee_id > 100 
        GROUP BY
         department_id 
        HAVING
         sum( salary )> 10000 
        ORDER BY 2 
         LIMIT 3 offset 2
         LOCK IN SHARE MODE;
        
        SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
          FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
          LINES TERMINATED BY '\n'
          FROM t_Opengauss_Function_Sqlines_Case0067_1;
          
        SELECT a,b,a+b INTO DUMPFILE '/tmp/result.txt'
          FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
          LINES TERMINATED BY '\n'
          FROM t_Opengauss_Function_Sqlines_Case0067_1;
        """
        self.log.info(cmd1)
        self.root.sh(cmd1)
        cmd2 = f'ls {self.in_file}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        self.assertEqual(res, f'{self.in_file}', '执行失败' + step_txt)

        step_txt = '----step3:执行sqlines命令，expect: 转换成功，' \
                   '删除DISTINCTROW关键字、删除HIGH_PRIORITY、STRAIGHT_JOIN、' \
                   'SQL_SMALL_RESULT、SQL_BIG_RESULT、SQL_BUFFER_RESULT、' \
                   'SQL_CACHE、SQL_NO_CACHE、SQL_CALC_FOUND_ROWS关键字'
        self.log.info(step_txt)
        cmd = f"{self.source_path};" \
            f"sqlines -s=mysql -t=opengauss -in={self.in_file}" \
            f" -out={self.out_file} -log={self.log_file}"
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.assertNotIn('error', res, '执行失败：')
        cmd2 = f'cat {self.out_file}'
        self.log.info(cmd2)
        res = self.root.sh(cmd2).result()
        self.log.info(res)
        check_list = ["DISTINCTROW",
                      "HIGH_PRIORITY",
                      "SQL_BIG_RESULT",
                      "SQL_BUFFER_RESULT",
                      "SQL_CACHE",
                      "SQL_CALC_FOUND_ROWS",
                      "SQL_NO_CACHE",
                      "SQL_SMALL_RESULT",
                      "STRAIGHT_JOIN"
                      ]
        for i in check_list:
            self.assertNotIn(i, res, '执行失败：' + step_txt)

    def tearDown(self):
        step_txt = '----step4:环境清理，expect: 环境清理成功----'
        self.log.info(step_txt)
        self.log.info('--------------------卸载sqlines--------------------')
        cmd = f'cd {os.path.join(self.local_realpath,self.unzip_dir)};' \
            f'sh build.sh -m'
        self.log.info(cmd)
        res = self.root.sh(cmd).result()
        self.log.info(res)
        self.log.info('--------------------删除相关文件--------------------')
        cmd2 = f'rm -rf {self.local_realpath} ' \
            f'{self.in_file} {self.out_file} {self.log_file};' \
            f'if [ -d {self.local_realpath} ] ;' \
            f'then echo "exists" ;else echo "not exists"; fi '
        self.log.info(cmd2)
        res2 = self.root.sh(cmd2).result()
        self.assertIn('Uninstall Sqlines successfully', res,
                      'Sqlines卸载失败：' + step_txt)
        self.assertIn('not exists', res2, '删除文件失败' + cmd2)
        self.log.info(f'----- {os.path.basename(__file__)} end-----')
